﻿Imports System.Data.SqlClient
Public Class dbStudent
    Dim Var As New varDeclare

    Public Function GetBaseList() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.ConnString)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT StudentID, StudentFullName 'Full Name', StudentAddress 'Address', StudentCity 'City', StudentPhoneM 'Mobile Phone', StudentPhoneH 'Home Phone', StudentPhoneO 'Office Phone' FROM v_StudentList"
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetStudentDetail(stdGenID As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.ConnString)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GetStudentDetail"
        command.Parameters.Add("@stdGenID", SqlDbType.VarChar).Value = stdGenID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetStudentBillDetail(stdGenID As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.ConnString)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GetStudentBillDetail"
        command.Parameters.Add("@stdGenID", SqlDbType.VarChar).Value = stdGenID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function StudentProfileManagement(Type As String, stdGenID As String, stdFullName As String, stdAddress As String, stdCity As String, _
                                             stdPhoneMobile As String, stdPhoneHome As String, stdPhoneOffice As String, stdPicture As String, _
                                             UserID As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.ConnString)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_StudentProfileManagement"
        command.Parameters.Add("@Type", SqlDbType.VarChar).Value = Type
        command.Parameters.Add("@stdGenID", SqlDbType.VarChar).Value = stdGenID
        command.Parameters.Add("@stdFullName", SqlDbType.VarChar).Value = stdFullName
        command.Parameters.Add("@stdAddress", SqlDbType.VarChar).Value = stdAddress
        command.Parameters.Add("@stdCity", SqlDbType.VarChar).Value = stdCity
        command.Parameters.Add("@stdPhoneMobile", SqlDbType.VarChar).Value = stdPhoneMobile
        command.Parameters.Add("@stdPhoneHome", SqlDbType.VarChar).Value = stdPhoneHome
        command.Parameters.Add("@stdPhoneOffice", SqlDbType.VarChar).Value = stdPhoneOffice
        If (stdPicture <> "") Then
            command.Parameters.Add("@stdPicture", SqlDbType.Image).Value = IO.File.ReadAllBytes(stdPicture)
        Else
            command.Parameters.Add("@stdPicture", SqlDbType.Image).Value = DBNull.Value
        End If
        command.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function
End Class
